﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms.VisualStyles;
using System.Windows.Forms;

namespace WHControlLib.CommonHelpers
{
/// <summary>
/// Excel文件导入导出类因为要引用NOIP所有目前注释掉了
/// </summary>
    public class ExcelHelper
    {

        #region Excel导入为Datatabel


        /// <summary>
        ///  不能为静态方法-Excel某sheet中内容导入到DataTable中
        /// 区分xsl和xslx分别处理
        /// </summary>
        /// <param name="filePath">Excel文件路径,含文件全名</param>
        /// <param name="sheetName">此Excel中sheet名</param>
        /// <returns></returns>
        //public DataTable ExcelSheetImportToDataTable(string filePath, string sheetName)
        //{

        //    DataTable dt = new DataTable();

        //    if (Path.GetExtension(filePath).ToLower() == ".xls".ToLower())
        //    {//.xls
        //        #region .xls文件处理:HSSFWorkbook
        //        HSSFWorkbook hssfworkbook;


        //        try
        //        {
        //            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
        //            {

        //                hssfworkbook = new HSSFWorkbook(file);
        //            }
        //        }
        //        catch (Exception e)
        //        {
        //            throw e;
        //        }

        //        ISheet sheet = hssfworkbook.GetSheet(sheetName);
        //        System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
        //        HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);

        //        //一行最后一个方格的编号 即总的列数 
        //        for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
        //        {
        //            //SET EVERY COLUMN NAME
        //            HSSFCell cell = (HSSFCell)headerRow.GetCell(j);

        //            dt.Columns.Add(cell.ToString());
        //        }

        //        while (rows.MoveNext())
        //        {
        //            IRow row = (HSSFRow)rows.Current;
        //            DataRow dr = dt.NewRow();

        //            if (row.RowNum == 0) continue;//The firt row is title,no need import

        //            for (int i = 0; i < row.LastCellNum; i++)
        //            {
        //                if (i >= dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213
        //                {
        //                    break;
        //                }

        //                ICell cell = row.GetCell(i);

        //                if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
        //                {
        //                    break;
        //                }

        //                if (cell == null)
        //                {
        //                    dr[i] = null;
        //                }
        //                else
        //                {
        //                    dr[i] = cell.ToString();
        //                }
        //            }

        //            dt.Rows.Add(dr);
        //        }
        //        #endregion
        //    }
        //    else
        //    {//.xlsx
        //        #region .xlsx文件处理:XSSFWorkbook
        //        XSSFWorkbook hssfworkbook;


        //        try
        //        {
        //            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
        //            {

        //                hssfworkbook = new XSSFWorkbook(file);
        //            }
        //        }
        //        catch (Exception e)
        //        {
        //            throw e;
        //        }

        //        ISheet sheet = hssfworkbook.GetSheet(sheetName);
        //        System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
        //        XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);



        //        //一行最后一个方格的编号 即总的列数 
        //        for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
        //        {
        //            //SET EVERY COLUMN NAME
        //            XSSFCell cell = (XSSFCell)headerRow.GetCell(j);

        //            dt.Columns.Add(cell.ToString());

        //        }

        //        while (rows.MoveNext())
        //        {
        //            IRow row = (XSSFRow)rows.Current;
        //            DataRow dr = dt.NewRow();

        //            if (row.RowNum == 0) continue;//The firt row is title,no need import

        //            for (int i = 0; i < row.LastCellNum; i++)
        //            {
        //                if (i >= dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213
        //                {
        //                    break;
        //                }

        //                ICell cell = row.GetCell(i);

        //                if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
        //                {
        //                    break;
        //                }

        //                if (cell == null)
        //                {
        //                    dr[i] = null;
        //                }
        //                else
        //                {
        //                    dr[i] = cell.ToString();
        //                }
        //            }
        //            dt.Rows.Add(dr);
        //        }
        //        #endregion
        //    }
        //    return dt;
        //}



        /// <summary>
        /// 读取Excel文件到DataTable
        /// </summary>
        /// <param name="FilePath">Excel文件路径</param>
        /// <param name="hasTitle">一般用true</param>
        /// <returns>datatable</returns>
        public static DataTable GetDataTableFromExcelByConn(string FilePath, bool hasTitle = false)
        {
            string SheetName;
            //OpenFileDialog openFile = new OpenFileDialog();
            //openFile.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
            //openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            //openFile.Multiselect = false;
            //if (openFile.ShowDialog() == DialogResult.Cancel) return null;
            //var filePath = openFile.FileName;
            string fileType = System.IO.Path.GetExtension(FilePath);
            if (string.IsNullOrEmpty(fileType)) return null;

            using (DataSet ds = new DataSet())
            {
                string strCon = string.Format("Provider=Microsoft.Jet.OLEDB.{0}.0;" +
                                "Extended Properties=\"Excel {1}.0;HDR={2};IMEX=1;\";" +
                                "data source={3};",
                                (fileType == ".xls" ? 4 : 12), (fileType == ".xls" ? 8 : 12), (hasTitle ? "Yes" : "NO"), FilePath);
                //string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";

                //string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; // Office 07及以上版本 不能出现多余的空格 而且分号注意                //string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; //Office 07以下版本 
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";" + "Extended Properties=Excel 8.0;";
                OleDbConnection conn = new OleDbConnection(strConn);

                try
                {
                    conn.Open();
                    DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                    SheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();

                    conn.Close();
                }
                catch (Exception e)
                {
                    conn.Close();
                    MessageBox.Show(e.Message);

                    return null;

                }

                conn.Close();
                string strCom = "SELECT * FROM " + "[" + SheetName + "]";
                using (OleDbConnection myConn = new OleDbConnection(strCon))
                using (OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn))
                {
                    try
                    {
                        myConn.Open();



                        myCommand.Fill(ds);
                    }
                    catch (Exception e)
                    {

                        myConn.Close();
                        MessageBox.Show(e.Message);

                        return null;
                    }

                }
                if (ds == null || ds.Tables.Count <= 0) return null;
                return ds.Tables[0];
            }
        }




        #endregion


        #region 导出为Excel

        /// <summary>
        /// NPOI导出Excel，不依赖本地是否装有Excel，导出速度快
        /// </summary>
        /// <param name="dataGridView1">要导出的dataGridView控件</param>
        /// <param name="sheetName">sheet表名</param>
        ///

        //public static void DataGridViewExportToExcel(DataGridView dataGridView1, string sheetName, string FileName)
        //{
        //    SaveFileDialog fileDialog = new SaveFileDialog();
        //    fileDialog.Filter = "Excel(97-2003)|*.xls";
        //    fileDialog.FileName = FileName;
        //    if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
        //    {
        //        return;
        //    }
        //    //不允许dataGridView显示添加行，负责导出时会报最后一行未实例化错误
        //    dataGridView1.AllowUserToAddRows = false;
        //    HSSFWorkbook workbook = new HSSFWorkbook();
        //    ISheet sheet = workbook.CreateSheet(sheetName);
        //    IRow rowHead = sheet.CreateRow(0);

        //    //填写表头
        //    for (int i = 0; i < dataGridView1.Columns.Count; i++)
        //    {
        //        rowHead.CreateCell(i, CellType.String).SetCellValue(dataGridView1.Columns[i].HeaderText.ToString());
        //    }
        //    //填写内容
        //    for (int i = 0; i < dataGridView1.Rows.Count; i++)
        //    {
        //        IRow row = sheet.CreateRow(i + 1);
        //        for (int j = 0; j < dataGridView1.Columns.Count; j++)
        //        {
        //
        //          //当cell内没有数据时添加空cell格子
        //            if (dataGridView1.Rows[i].Cells[j].Value==null)
        //            {
        //               var c = row.CreateCell(j);
        //               c.SetBlank();
        //            }
        //            else
        //
        //
        //            row.CreateCell(j, CellType.String).SetCellValue(dataGridView1.Rows[i].Cells[j].Value.ToString());
        //        }
        //    }

        //    using (FileStream stream = File.OpenWrite(fileDialog.FileName))
        //    {
        //        workbook.Write(stream);
        //        stream.Close();
        //    }
        //    MessageBox.Show("导出数据成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        //    GC.Collect();
        //}


        /// <summary>
        /// 将datatable流文件导出到指定路径的Excel中
        /// </summary>
        /// <param name="sourceTable"></param>
        /// <param name="OutFileName"></param>
        //public static void DataTableExportExcel(DataTable sourceTable, string OutFileName)
        //{
        //    MemoryStream ms = DataTableToExcel(sourceTable);
        //    string saveFileName = "";
        //    bool fileSaved = false;
        //    SaveFileDialog saveDialog = new SaveFileDialog();//打开选择保存窗口
        //    saveDialog.DefaultExt = "xls";
        //    saveDialog.Filter = "Excel文件|*.xls";//保存窗口的文件筛选
        //    saveDialog.FileName = OutFileName;//默认文件名称
        //    saveDialog.ShowDialog();
        //    saveFileName = saveDialog.FileName;
        //    if (saveFileName.IndexOf(":") < 0) return; //用户点了取消
        //    if (saveFileName != "")
        //    {
        //        try
        //        {
        //            FileStream fs = new FileStream(saveDialog.FileName, FileMode.Create);
        //            fs.Write(ms.GetBuffer(), 0, ms.GetBuffer().Length);
        //            ms.Close();
        //            ms.Dispose();
        //            fs.Close();
        //            fileSaved = true;
        //        }
        //        catch (Exception ex)
        //        {
        //            fileSaved = false;
        //            MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
        //        }
        //    }
        //    else
        //    {
        //        fileSaved = false;
        //    }
        //    GC.Collect();//强行销毁
        //    if (fileSaved && File.Exists(saveFileName))
        //    {
        //        MessageBox.Show("导出成功！", "通知");
        //    }
        //    else
        //    {
        //        MessageBox.Show("导出失败！", "通知");
        //    }
        //}
        /// <summary>
        /// 将datatable中的数据放入内存流中
        /// </summary>
        /// <param name="souruceTable">来源datatable</param>
        /// <returns></returns>
        //private static MemoryStream DataTableToExcel(DataTable souruceTable)
        //{
        //    int rowCount = souruceTable.Rows.Count;
        //    int colCount = souruceTable.Columns.Count;
        //    HSSFWorkbook workbook = new HSSFWorkbook();//新建一个工作簿
        //    ISheet sheet = workbook.CreateSheet("mysheet");//新建一个sheet页
        //    IRow headerRow = sheet.CreateRow(0);
        //    MemoryStream ms = new MemoryStream();

        //    //构建标题行
        //    foreach (DataColumn col in souruceTable.Columns)
        //    {
        //        headerRow.CreateCell(col.Ordinal, CellType.String).SetCellValue(col.Caption);
        //    }

        //    //构建数据行
        //    for (int i = 0; i < rowCount; i++)
        //    {
        //        IRow dataRow = sheet.CreateRow(i + 1);
        //        for (int j = 0; j < colCount; j++)
        //        {
        //            DataRow row = souruceTable.Rows[i];
        //            DataColumn col = souruceTable.Columns[j];
        //            dataRow.CreateCell(j).SetCellValue(row[col].ToString());
        //        }
        //    }
        //    workbook.Write(ms);
        //    ms.Flush();
        //    ms.Position = 0;
        //    sheet = null;
        //    headerRow = null;
        //    workbook = null;
        //    return ms;
        //}



        #endregion

    }
}
